﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapVVPACK(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveVVPACK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryVVPACK(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapVVPACK(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertVVPACK(dwLocal, ITXDBVERSION);
            }
            UpdateVVPACK(dwLocal);
        }
        public void InsertVVPACK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            string table = "VVPACK";  //VVPMANDANT	VVPLIEFSCH	VVPPACKNR
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "50.24":
                    or1.CommandText = "INSERT INTO VVPACK(VVPMANDANT, VVPLIEFSCH, VVPPACKNR, VVPAENDZL, VVPAENDBEN, VVPAENDDAT, VVPUEBLIEFSCH, VVPINPACKNR, VVPPADNR, VVPPACKTYP, VVPBRUTTO, VVPNETTO, VVPTARA, VVPWERT, VVPFRACHTBE, VVPZUSCHLAG, VVPFRNETTO, VVPNEBENKO, VVPWAEHRSLN, VVPLIEFNR, VVPRENR, VVPREPOS, VVPREDAT, VVPTPMA, VVPTPMNR, VVPPORTO, VVPRAUFKRS, VVPRAUFLFD, VVPRAUFPOS, VVPTEILNR, VVPVOLUMEN, VVPLAENGE, VVPBREITE, VVPHOEHE, VVPKZMANUELL, VVPHUELGEW, VVPHANGEW, VVPSPULEN, VVPPKDNR, VVPGEWGEW, VVPENDE, VVPSHIPM, VVPAUTOPN, VVPSERVICE, VVPOHNEBST, VVPRAMPE, VVPFILLER)"
                    + " VALUES (:VVPMANDANT, :VVPLIEFSCH, :VVPPACKNR, 0, '        ', 0, :VVPLIEFSCH, '0000', '                         ', 'NULL', 531, 531, 0, 0, '0000000000', 0, 0, 0, '   ', '        ', '          ', '0000', 0, '    ', '00000000', 0, '  ', '00000000', '000', '0000', 0, 0, 0, 0, ' ', 0, 0, 12, '                         ', ' ', ' ', '     ', ' ', '   ', ' ', ' ', '                              ')";
                    break;
                default:
                    break;
            }
            or1.Parameters.Add("VVPMANDANT", OracleType.NVarChar).Value = dwData["VVPMANDANT"].ToString();
            or1.Parameters.Add("VVPLIEFSCH", OracleType.NVarChar).Value = dwData["VVPLIEFSCH"].ToString();
            or1.Parameters.Add("VVPPACKNR", OracleType.NVarChar).Value = dwData["VVPPACKNR"].ToString();
            Connect2Proxy(or1);
        }
        public DataSet QueryVVPACK(DataRow dwData, int option = 1)
        {
            DataSet Return = new DataSet();
            DataTable tbLocal = null;
            DataSet Result = new DataSet();
            string table = "VVPACK";  //VVPMANDANT	VVPLIEFSCH	VVPPACKNR

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM VVPACK WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM VVPACK WHERE VVPMANDANT=:VVPMANDANT AND VVPLIEFSCH=:VVPLIEFSCH AND VVPPACKNR=:VVPPACKNR";
                    or1.Parameters.Add("VVPMANDANT", OracleType.NVarChar).Value = dwData["VVPMANDANT"].ToString();
                    or1.Parameters.Add("VVPLIEFSCH", OracleType.NVarChar).Value = dwData["VVPLIEFSCH"].ToString();
                    or1.Parameters.Add("VVPPACKNR", OracleType.NVarChar).Value = dwData["VVPPACKNR"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT P.* FROM VVPACK P JOIN VVLIEF V ON VVPMANDANT=VVMANDANT AND VVPLIEFSCH=VVLIEFSCH JOIN VVTLIEF T ON V.VVMANDANT=VVTMANDANT AND VVLIEFSCH=VVTLIEFSCH"
               + " WHERE VVTMANDANT=:VVTMANDANT AND VVTAUFKRS =:VVTAUFKRS AND VVTAUFLFD=:VVTAUFLFD";
                    or1.Parameters.Add("VVTMANDANT", OracleType.NVarChar).Value = dwData["VVTMANDANT"].ToString();
                    or1.Parameters.Add("VVTAUFKRS", OracleType.NVarChar).Value = dwData["VVTAUFKRS"].ToString();
                    or1.Parameters.Add("VVTAUFLFD", OracleType.NVarChar).Value = dwData["VVTAUFLFD"].ToString();               
                    break;
                case 3:
                    or1.CommandText = "SELECT VVLIEFSCH,P.* FROM VVPACK P  JOIN VVLIEF ON VVLIEFSCH=VVPLIEFSCH  WHERE  VVNEUDAT>='" + dwData["DSTART"].ToString() + "' AND VVNEUDAT<='" + dwData["DEND"].ToString() + "' AND VVSTATUS IN (" + dwData["C20"].ToString() + ")"; 
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "VVPACK";
            }
            Return.Merge(Result);

            return Return;
        }
        public void UpdateVVPACK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            DataSet result = new DataSet();
            or1 = OracleDB.CreateCommand();

            #region====Command====
            or1.CommandText = "UPDATE VVPACK SET "
                + "VVPMANDANT=:VVPMANDANT,"
                + "VVPLIEFSCH=:VVPLIEFSCH,"
                + "VVPPACKNR=:VVPPACKNR,"
                + "VVPAENDZL=:VVPAENDZL,"
                + "VVPAENDBEN=:VVPAENDBEN,"
                + "VVPAENDDAT=:VVPAENDDAT,"
                + "VVPUEBLIEFSCH=:VVPUEBLIEFSCH,"
                + "VVPINPACKNR=:VVPINPACKNR,"
                + "VVPPADNR=:VVPPADNR,"
                + "VVPPACKTYP=:VVPPACKTYP,"
                + "VVPBRUTTO=:VVPBRUTTO,"
                + "VVPNETTO=:VVPNETTO,"
                + "VVPTARA=:VVPTARA,"
                + "VVPWERT=:VVPWERT,"
                + "VVPFRACHTBE=:VVPFRACHTBE,"
                + "VVPZUSCHLAG=:VVPZUSCHLAG,"
                + "VVPFRNETTO=:VVPFRNETTO,"
                + "VVPNEBENKO=:VVPNEBENKO,"
                + "VVPWAEHRSLN=:VVPWAEHRSLN,"
                + "VVPLIEFNR=:VVPLIEFNR,"
                + "VVPRENR=:VVPRENR,"
                + "VVPREPOS=:VVPREPOS,"
                + "VVPREDAT=:VVPREDAT,"
                + "VVPTPMA=:VVPTPMA,"
                + "VVPTPMNR=:VVPTPMNR,"
                + "VVPPORTO=:VVPPORTO,"
                + "VVPRAUFKRS=:VVPRAUFKRS,"
                + "VVPRAUFLFD=:VVPRAUFLFD,"
                + "VVPRAUFPOS=:VVPRAUFPOS,"
                + "VVPTEILNR=:VVPTEILNR,"
                + "VVPVOLUMEN=:VVPVOLUMEN,"
                + "VVPLAENGE=:VVPLAENGE,"
                + "VVPBREITE=:VVPBREITE,"
                + "VVPHOEHE=:VVPHOEHE,"
                + "VVPKZMANUELL=:VVPKZMANUELL,"
                + "VVPHUELGEW=:VVPHUELGEW,"
                + "VVPHANGEW=:VVPHANGEW,"
                + "VVPSPULEN=:VVPSPULEN,"
                + "VVPPKDNR=:VVPPKDNR,"
                + "VVPGEWGEW=:VVPGEWGEW,"
                + "VVPENDE=:VVPENDE,"
                + "VVPSHIPM=:VVPSHIPM,"
                + "VVPAUTOPN=:VVPAUTOPN,"
                + "VVPSERVICE=:VVPSERVICE,"
                + "VVPOHNEBST=:VVPOHNEBST,"
                + "VVPRAMPE=:VVPRAMPE,"
                + "VVPFILLER=:VVPFILLER"
                + " WHERE  "
                + "  VVTMANDANT=:VVTMANDANT AND VVTLIEFSCH=:VVTLIEFSCH AND VVTLIEFSCHPOS=:VVTLIEFSCHPOS";
            #endregion

            #region====Parameters====
            or1.Parameters.Add("VVPMANDANT", OracleType.NVarChar).Value = dwData["VVPMANDANT"].ToString();
            or1.Parameters.Add("VVPLIEFSCH", OracleType.NVarChar).Value = dwData["VVPLIEFSCH"].ToString();
            or1.Parameters.Add("VVPPACKNR", OracleType.NVarChar).Value = dwData["VVPPACKNR"].ToString();
            or1.Parameters.Add("VVPAENDZL", OracleType.NVarChar).Value = dwData["VVPAENDZL"].ToString();
            or1.Parameters.Add("VVPAENDBEN", OracleType.NVarChar).Value = dwData["VVPAENDBEN"].ToString();
            or1.Parameters.Add("VVPAENDDAT", OracleType.NVarChar).Value = dwData["VVPAENDDAT"].ToString();
            or1.Parameters.Add("VVPUEBLIEFSCH", OracleType.NVarChar).Value = dwData["VVPUEBLIEFSCH"].ToString();
            or1.Parameters.Add("VVPINPACKNR", OracleType.NVarChar).Value = dwData["VVPINPACKNR"].ToString();
            or1.Parameters.Add("VVPPADNR", OracleType.NVarChar).Value = dwData["VVPPADNR"].ToString();
            or1.Parameters.Add("VVPPACKTYP", OracleType.NVarChar).Value = dwData["VVPPACKTYP"].ToString();
            or1.Parameters.Add("VVPBRUTTO", OracleType.NVarChar).Value = dwData["VVPBRUTTO"].ToString();
            or1.Parameters.Add("VVPNETTO", OracleType.NVarChar).Value = dwData["VVPNETTO"].ToString();
            or1.Parameters.Add("VVPTARA", OracleType.NVarChar).Value = dwData["VVPTARA"].ToString();
            or1.Parameters.Add("VVPWERT", OracleType.NVarChar).Value = dwData["VVPWERT"].ToString();
            or1.Parameters.Add("VVPFRACHTBE", OracleType.NVarChar).Value = dwData["VVPFRACHTBE"].ToString();
            or1.Parameters.Add("VVPZUSCHLAG", OracleType.NVarChar).Value = dwData["VVPZUSCHLAG"].ToString();
            or1.Parameters.Add("VVPFRNETTO", OracleType.NVarChar).Value = dwData["VVPFRNETTO"].ToString();
            or1.Parameters.Add("VVPNEBENKO", OracleType.NVarChar).Value = dwData["VVPNEBENKO"].ToString();
            or1.Parameters.Add("VVPWAEHRSLN", OracleType.NVarChar).Value = dwData["VVPWAEHRSLN"].ToString();
            or1.Parameters.Add("VVPLIEFNR", OracleType.NVarChar).Value = dwData["VVPLIEFNR"].ToString();
            or1.Parameters.Add("VVPRENR", OracleType.NVarChar).Value = dwData["VVPRENR"].ToString();
            or1.Parameters.Add("VVPREPOS", OracleType.NVarChar).Value = dwData["VVPREPOS"].ToString();
            or1.Parameters.Add("VVPREDAT", OracleType.NVarChar).Value = dwData["VVPREDAT"].ToString();
            or1.Parameters.Add("VVPTPMA", OracleType.NVarChar).Value = dwData["VVPTPMA"].ToString();
            or1.Parameters.Add("VVPTPMNR", OracleType.NVarChar).Value = dwData["VVPTPMNR"].ToString();
            or1.Parameters.Add("VVPPORTO", OracleType.NVarChar).Value = dwData["VVPPORTO"].ToString();
            or1.Parameters.Add("VVPRAUFKRS", OracleType.NVarChar).Value = dwData["VVPRAUFKRS"].ToString();
            or1.Parameters.Add("VVPRAUFLFD", OracleType.NVarChar).Value = dwData["VVPRAUFLFD"].ToString();
            or1.Parameters.Add("VVPRAUFPOS", OracleType.NVarChar).Value = dwData["VVPRAUFPOS"].ToString();
            or1.Parameters.Add("VVPTEILNR", OracleType.NVarChar).Value = dwData["VVPTEILNR"].ToString();
            or1.Parameters.Add("VVPVOLUMEN", OracleType.NVarChar).Value = dwData["VVPVOLUMEN"].ToString();
            or1.Parameters.Add("VVPLAENGE", OracleType.NVarChar).Value = dwData["VVPLAENGE"].ToString();
            or1.Parameters.Add("VVPBREITE", OracleType.NVarChar).Value = dwData["VVPBREITE"].ToString();
            or1.Parameters.Add("VVPHOEHE", OracleType.NVarChar).Value = dwData["VVPHOEHE"].ToString();
            or1.Parameters.Add("VVPKZMANUELL", OracleType.NVarChar).Value = dwData["VVPKZMANUELL"].ToString();
            or1.Parameters.Add("VVPHUELGEW", OracleType.NVarChar).Value = dwData["VVPHUELGEW"].ToString();
            or1.Parameters.Add("VVPHANGEW", OracleType.NVarChar).Value = dwData["VVPHANGEW"].ToString();
            or1.Parameters.Add("VVPSPULEN", OracleType.NVarChar).Value = dwData["VVPSPULEN"].ToString();
            or1.Parameters.Add("VVPPKDNR", OracleType.NVarChar).Value = dwData["VVPPKDNR"].ToString();
            or1.Parameters.Add("VVPGEWGEW", OracleType.NVarChar).Value = dwData["VVPGEWGEW"].ToString();
            or1.Parameters.Add("VVPENDE", OracleType.NVarChar).Value = dwData["VVPENDE"].ToString();
            or1.Parameters.Add("VVPSHIPM", OracleType.NVarChar).Value = dwData["VVPSHIPM"].ToString();
            or1.Parameters.Add("VVPAUTOPN", OracleType.NVarChar).Value = dwData["VVPAUTOPN"].ToString();
            or1.Parameters.Add("VVPSERVICE", OracleType.NVarChar).Value = dwData["VVPSERVICE"].ToString();
            or1.Parameters.Add("VVPOHNEBST", OracleType.NVarChar).Value = dwData["VVPOHNEBST"].ToString();
            or1.Parameters.Add("VVPRAMPE", OracleType.NVarChar).Value = dwData["VVPRAMPE"].ToString();
            or1.Parameters.Add("VVPFILLER", OracleType.NVarChar).Value = dwData["VVPFILLER"].ToString();
            #endregion

            Connect2Proxy(or1);
        }
    }
}
